USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TOTAL_CUOTAS]    Script Date: 11/28/2011 12:07:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0003_TOTAL_CUOTAS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0003_TOTAL_CUOTAS]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TOTAL_CUOTAS]    Script Date: 11/28/2011 12:07:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_M0003_TOTAL_CUOTAS]
AS


BEGIN 
	SELECT	b.ppsuc as suc,
			b.ppcta as cta,
			b.ppoper as ope ,
			max(convert(numeric,b.ppcap + case  
							when d.ppimp9 is not null then  b.ppint- d.ppimp9
						   else b.ppint end) )  as valcuot,
			count(b.ppfvto) as totcuotas,
			convert(varchar(8),b.d601fc,112) as  fecha_grabacion
			
	INTO	dbo.prima_comision_total_cuotas
	
	FROM	FSD601 b (nolock)
	LEFT JOIN FSD611 d (nolock) ON  b.ppcta = d.ppcta 
								and b.ppoper = d.ppoper 
								and b.ppsuc = d.ppsuc 
								and	b.ppfpag = d.ppfpag 
								and	b.ppmod =  d.ppmod 
								and	b.Ppmda =  d.Ppmda
								and b.ppsbop = d.ppsbop 
								and	b.pptope = d.pptope
	
	WHERE	b.D601CO ='S' 
			and		b.ppsbop = 0 
			and		d.ppexte = 0 
			and		d.pptipo = 'M'  
			--b.ppcta  *= d.ppcta and
			--b.ppoper *= d.ppoper and
			--b.ppsuc  *= d.ppsuc and
			--b.ppfpag *= d.ppfpag and
			--b.ppmod *=  d.ppmod and
			--b.Ppmda *=  d.Ppmda and
			--b.ppsbop *= d.ppsbop and
			--b.pptope *= d.pptope and
		
	GROUP BY b.ppsuc, b.ppcta, b.ppoper, b.d601fc  

END
GO

